package com.dal;

import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class Main {

    private static final Logger logger = LoggerFactory.getLogger(Main.class);

    public static void main(String[] args) throws Exception {
        InputStream input = new FileInputStream("/Users/jian/Downloads/财管已被使用供应商-2022-12-12(1).xlsx");
        Map<String, List<List<ExcelCellVo>>> listMap = analyzeExcel(input);

        ExcelReader reader = ExcelUtil.getReader("/Users/jian/Downloads/财管已被使用供应商-2022-12-12(1).xlsx");
        List<Map<String,Object>> readAll = reader.readAll();
        logger.info(JSONUtil.toJsonPrettyStr(readAll));
        //debug看结果
        logger.info(JSONUtil.toJsonPrettyStr(listMap));
    }

    /**
     * 分析excl
     *
     * @param is
     * @return
     * @throws Exception
     */
    public static Map<String, List<List<ExcelCellVo>>> analyzeExcel(InputStream is) throws Exception {
        try {
            Workbook wb = new XSSFWorkbook(is);
            Map<String, List<List<ExcelCellVo>>> sheets = new HashMap(wb.getNumberOfSheets());//根据sheet页创建map集合
            for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); ++sheetNum) {
                //读取sheet页
                Sheet sheet = wb.getSheetAt(sheetNum);
                if (sheet != null) {
                    // 当前sheet页名
                    String sheetName = sheet.getSheetName();
                    List<List<ExcelCellVo>> data = new ArrayList<>();//创建list集合
                    // 遍历行
                    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                        //获得行
                        Row row = sheet.getRow(i);
                        List<ExcelCellVo> colData = new ArrayList<>();//创建list集合
                        //遍历列
                        if (row != null) {
                            for (int j = 0; j < row.getLastCellNum(); j++) {
                                //获取单元格
                                Cell cell = row.getCell(j);
                                Font eFont = wb.getFontAt(cell.getCellStyle().getFontIndex());//获取字体
                                colData.add(getCellVo(cell, eFont));
                            }
                            data.add(colData);
                        }
                    }
                    sheets.put(sheetName, data);
                }
            }
            return sheets;
        } catch (Exception e) {
            logger.error("analyzeExcel error : {}", e);
        }
        return null;
    }

    /**
     * 获取单元格vo
     *
     * @param cell  单元格
     * @param eFont 字体
     * @return
     */
    private static ExcelCellVo getCellVo(Cell cell, Font eFont) {
        ExcelCellVo dto = new ExcelCellVo();//创建单元格对象
        if (cell != null && eFont != null) {
            //值
            switch (cell.getCellType()) {
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", LocaleUtil.getUserLocale());
                        sdf.setTimeZone(LocaleUtil.getUserTimeZone());
                        dto.setValue(sdf.format(cell.getDateCellValue()));
                    } else {
                        DataFormatter formatter = new DataFormatter();
                        dto.setValue(formatter.formatCellValue(cell));
                    }
                    break;
                case STRING:
                    dto.setValue(cell.getStringCellValue());
                    break;
                case BOOLEAN:
                    dto.setValue(String.valueOf(cell.getBooleanCellValue()));
                    break;
                case FORMULA:
                    dto.setValue(cell.getCellFormula());
                    break;
                case BLANK:
                    dto.setValue("");
                    break;
                case ERROR:
                    dto.setValue(ErrorEval.getText(cell.getErrorCellValue()));
                    break;
                default:
                    dto.setValue("Unknown Cell Type: " + cell.getCellType());
            }
            // xlsx 07版
            //背景颜色
            CellStyle cellStyle = cell.getCellStyle();
            XSSFColor xssfColor = (XSSFColor) cellStyle.getFillForegroundColorColor();
            byte[] bytes;
            if (xssfColor != null) {
                bytes = xssfColor.getRGB();
                dto.setBackgroundColor(String.format("#%02X%02X%02X", bytes[0], bytes[1], bytes[2]));
            }
            //字体颜色
            XSSFFont f = (XSSFFont) eFont;//获取字体
            XSSFColor xssffont = f.getXSSFColor();
            byte[] rgb;
            if (xssffont != null) {
                rgb = xssffont.getRGB(); //得到rgb的byte数组
                dto.setFontColor(String.format("#%02X%02X%02X", rgb[0], rgb[1], rgb[2]));
            }
        }
        return dto;
    }
}